package com.hddf.project.service;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hddf.project.base.BaseService;
import com.hddf.project.dao.ProjectDao;
import com.hddf.project.impl.ProjectDaoImpl;
import com.hddf.project.util.ExcelUtil;
import com.hddf.project.util.O;

public class ExcelService extends BaseService {

	private static ExcelService excelService;
	private static ProjectDao pd;

	static {
		if (null == excelService) {
			excelService = new ExcelService();
		}
		if (null == pd) {
			pd = new ProjectDaoImpl();
		}
	}

	public static ExcelService getInService() {
		return excelService;
	}

	/**
	 * 生成通过的excel
	 * 
	 * @param path
	 * @param name
	 */
	public void passExcel(String path, String name) {
		String suffix = name.substring(name.lastIndexOf(".") + 1);
		switch (suffix) {
		case "xls":
			passXls(path);
			break;
		case "xlsx":
			passXlsx(path);
			break;
		}
	}

	public void passXls(String path) {
		try {
			FileInputStream is = new FileInputStream(path);
			HSSFWorkbook hwb = new HSSFWorkbook(is);
			FormulaEvaluator evaluator = hwb.getCreationHelper().createFormulaEvaluator();

			HSSFSheet sheet = hwb.getSheetAt(0);
			if (null != sheet) {
				for (int j = 6; j <= sheet.getLastRowNum(); j++) {
					HSSFRow row = sheet.getRow(j);
					if (null != row) {
						// 主管部门
						HSSFCell mgrDepartHc = row.getCell(9);
						// 建设部门
						HSSFCell buildSideHc = row.getCell(10);
						// 建设内容
						HSSFCell descriptionHc = row.getCell(8);
						// 区域
						HSSFCell areaHc = row.getCell(12);
						if (null == mgrDepartHc && null == buildSideHc && null == descriptionHc && null == areaHc) {
							sheet.removeRow(row);
							continue;
						}
						String mgrDepart = null;
						if (null != mgrDepartHc) {
							mgrDepart = ExcelUtil.getHSSF(mgrDepartHc, evaluator);
						}
						String buildSide = null;
						if (null != buildSideHc) {
							buildSide = ExcelUtil.getHSSF(buildSideHc, evaluator);
						}
						String description = null;
						if (null != descriptionHc) {
							description = ExcelUtil.getHSSF(descriptionHc, evaluator);
						}
						String area = null;
						if (null != areaHc) {
							area = ExcelUtil.getHSSF(areaHc, evaluator);
						}

						if (!O.ckSt(mgrDepart) && !O.ckSt(buildSide) && !O.ckSt(description) && !O.ckSt(area)) {
							sheet.removeRow(row);
						}
					}
				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				hwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void passXlsx(String path) {
		try {
			FileInputStream is = new FileInputStream(path);
			XSSFWorkbook xwb = new XSSFWorkbook(is);
			FormulaEvaluator evaluator = xwb.getCreationHelper().createFormulaEvaluator();

			XSSFSheet xs = xwb.getSheetAt(0);
			if (null != xs) {
				for (int j = 6; j <= xs.getLastRowNum(); j++) {
					XSSFRow xr = xs.getRow(j);
					if (null != xr) {
						// 主管部门
						XSSFCell mgrDepartHc = xr.getCell(9);
						// 建设部门
						XSSFCell buildSideHc = xr.getCell(10);
						// 建设内容
						XSSFCell descriptionHc = xr.getCell(8);
						// 区域
						XSSFCell areaHc = xr.getCell(12);
						if (null == mgrDepartHc && null == buildSideHc && null == descriptionHc && null == areaHc) {
							xs.removeRow(xr);
							continue;
						}
						String mgrDepart = null;
						if (null != mgrDepartHc) {
							mgrDepart = ExcelUtil.getXSSF(mgrDepartHc, evaluator);
						}
						String buildSide = null;
						if (null != buildSideHc) {
							buildSide = ExcelUtil.getXSSF(buildSideHc, evaluator);
						}
						String description = null;
						if (null != descriptionHc) {
							description = ExcelUtil.getXSSF(descriptionHc, evaluator);
						}
						String area = null;
						if (null != areaHc) {
							area = ExcelUtil.getXSSF(areaHc, evaluator);
						}
						if (!O.ckSt(mgrDepart) && !O.ckSt(buildSide) && !O.ckSt(description) && !O.ckSt(area)) {
							xs.removeRow(xr);
						}
					}
				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				xwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 生成未通过的excel
	 * 
	 * @param file
	 * @param name
	 */
	public void notPassExcel(String path, String name) {
		String suffix = name.substring(name.lastIndexOf(".") + 1);
		switch (suffix) {
		case "xls":
			notPassXls(path);
			break;
		case "xlsx":
			notPassXlsx(path);
			break;
		}
	}

	public void notPassXls(String path) {
		try {
			FileInputStream is = new FileInputStream(path);
			HSSFWorkbook hwb = new HSSFWorkbook(is);
			FormulaEvaluator evaluator = hwb.getCreationHelper().createFormulaEvaluator();

			HSSFSheet hs = hwb.getSheetAt(0);
			if (null != hs) {
				for (int j = 6; j <= hs.getLastRowNum(); j++) {
					HSSFRow hr = hs.getRow(j);
					if (null != hr) {
						// 主管部门
						HSSFCell mgrDepartHc = hr.getCell(9);
						// 建设部门
						HSSFCell buildSideHc = hr.getCell(10);
						// 建设内容
						HSSFCell descriptionHc = hr.getCell(8);
						// 区域
						HSSFCell areaHc = hr.getCell(12);

						String mgrDepart = null;
						if (null != mgrDepartHc) {
							mgrDepart = ExcelUtil.getHSSF(mgrDepartHc, evaluator);
						}
						String buildSide = null;
						if (null != buildSideHc) {
							buildSide = ExcelUtil.getHSSF(buildSideHc, evaluator);
						}
						String description = null;
						if (null != descriptionHc) {
							description = ExcelUtil.getHSSF(descriptionHc, evaluator);
						}
						String area = null;
						if (null != areaHc) {
							area = ExcelUtil.getHSSF(areaHc, evaluator);
						}
						if (O.ckSt(mgrDepart) || O.ckSt(buildSide) || O.ckSt(description) || O.ckSt(area)) {
							hs.removeRow(hr);
						}
					}

				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				hwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void notPassXlsx(String path) {
		try {
			FileInputStream is = new FileInputStream(path);
			XSSFWorkbook xwb = new XSSFWorkbook(is);
			FormulaEvaluator evaluator = xwb.getCreationHelper().createFormulaEvaluator();

			XSSFSheet xs = xwb.getSheetAt(0);
			if (null != xs) {
				for (int j = 6; j <= xs.getLastRowNum(); j++) {
					XSSFRow xr = xs.getRow(j);
					if (null != xr) {
						// 主管部门
						XSSFCell mgrDepartHc = xr.getCell(9);
						// 建设部门
						XSSFCell buildSideHc = xr.getCell(10);
						// 建设内容
						XSSFCell descriptionHc = xr.getCell(8);
						// 区域
						XSSFCell areaHc = xr.getCell(12);

						String mgrDepart = null;
						if (null != mgrDepartHc) {
							mgrDepart = ExcelUtil.getXSSF(mgrDepartHc, evaluator);
						}
						String buildSide = null;
						if (null != buildSideHc) {
							buildSide = ExcelUtil.getXSSF(buildSideHc, evaluator);
						}
						String description = null;
						if (null != descriptionHc) {
							description = ExcelUtil.getXSSF(descriptionHc, evaluator);
						}
						String area = null;
						if (null != areaHc) {
							area = ExcelUtil.getXSSF(areaHc, evaluator);
						}
						if (O.ckSt(mgrDepart) || O.ckSt(buildSide) || O.ckSt(description) || O.ckSt(area)) {
							xs.removeRow(xr);
						}
					}
				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				xwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 生成有ID的excel
	 * 
	 * @param path
	 * @param name
	 */
	public void idExcel(String path, String name, Map<String, Integer> map) {
		String suffix = name.substring(name.lastIndexOf(".") + 1);
		switch (suffix) {
		case "xls":
			idXls(path, map);
			break;
		case "xlsx":
			idXlsx(path, map);
			break;
		}
	}

	public void idXls(String path, Map<String, Integer> map) {
		try {
			FileInputStream is = new FileInputStream(path);
			HSSFWorkbook hwb = new HSSFWorkbook(is);
			FormulaEvaluator evaluator = hwb.getCreationHelper().createFormulaEvaluator();

			HSSFSheet hs = hwb.getSheetAt(0);
			if (null != hs) {
				HSSFFont hf = hwb.createFont();// 表头字体样式
				hf.setFontName("黑体");
				hf.setFontHeightInPoints((short) 18);// 字号
				HSSFCellStyle hcStyle = hwb.createCellStyle();// 表头样式
				hcStyle.setFont(hf);
				hcStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
				hcStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

				HSSFCell hcTitle = hs.getRow(5).createCell(27);
				hcTitle.setCellStyle(hcStyle);
				hcTitle.setCellValue("ID值");

				for (int j = 6; j <= hs.getLastRowNum(); j++) {
					HSSFRow hr = hs.getRow(j);
					if (null != hr) {
						// 项目名称
						HSSFCell nameHc = hr.getCell(1);
						if (null != nameHc) {
							String name = ExcelUtil.getHSSF(nameHc, evaluator);
							if (null != map.get(name)) {
								HSSFCell hc = hr.createCell(27);
								hc.setCellStyle(hcStyle);
								hc.setCellValue(map.get(name));
							}
						}
					}
				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				hwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void idXlsx(String path, Map<String, Integer> map) {
		try {
			FileInputStream is = new FileInputStream(path);
			XSSFWorkbook xwb = new XSSFWorkbook(is);
			FormulaEvaluator evaluator = xwb.getCreationHelper().createFormulaEvaluator();

			XSSFSheet xs = xwb.getSheetAt(0);
			if (null != xs) {
				XSSFFont xf = xwb.createFont();// 表头字体样式
				xf.setFontName("黑体");
				xf.setFontHeightInPoints((short) 18);// 字号
				XSSFCellStyle xcStyle = xwb.createCellStyle();// 表头样式
				xcStyle.setFont(xf);
				xcStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
				xcStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

				XSSFCell xcTitle = xs.getRow(5).createCell(27);
				xcTitle.setCellStyle(xcStyle);
				xcTitle.setCellValue("ID值");

				for (int j = 6; j <= xs.getLastRowNum(); j++) {
					XSSFRow xr = xs.getRow(j);
					if (null != xr) {
						// 项目名称
						XSSFCell nameXc = xr.getCell(1);
						String name = ExcelUtil.getXSSF(nameXc, evaluator);
						if (null != map.get(name)) {
							XSSFCell xc = xr.createCell(27);
							xc.setCellStyle(xcStyle);
							xc.setCellValue(map.get(name));
						}
					}
				}

				// 保存数据
				FileOutputStream fout = new FileOutputStream(path);
				xwb.write(fout);
				fout.close();
			}
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}